﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DacSanMienTayOBJ
{
    public class DistrictBusiness
    {
        public static bool InsertDistrict(District district, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "insert into district(ProvinceID,DistrictName,XCoor,YCoor,IsVisible) values(@provinceID,@districtName,@xCoor,@yCoor,@isVisible)";
                SqlParameter[] pa = new SqlParameter[5];
                pa[0] = new SqlParameter("@provinceID", district.ProvinceID);
                pa[1] = new SqlParameter("@districtName", district.DistrictName);
                pa[2] = new SqlParameter("@xCoor", district.XCoor);
                pa[3] = new SqlParameter("@yCoor", district.YCoor);
                pa[4] = new SqlParameter("@isVisible", district.IsVisible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool UpdateDistrict(int districtID,District district, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "update district set ProvinceID=@provinceID,DistrictName=@districtName where DistrictID=@districtID";
                SqlParameter[] pa = new SqlParameter[3];
                pa[0] = new SqlParameter("@provinceID", district.ProvinceID);
                pa[1] = new SqlParameter("@districtName", district.DistrictName);
                pa[2] = new SqlParameter("@districtID", districtID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool UpdateDistrictVisible(int districtID, int visible, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "update district set IsVisible=@isVisible where DistrictID=@districtID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@isVisible", visible);
                pa[1] = new SqlParameter("@districtID", districtID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<District> ListAllDistrict(ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<District> list = new List<District>();
                string sql = "select *from district";
                SqlCommand command = helper.GetCommandNonParameter(sql, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    District dis = new District();
                    dis.DistrictID = int.Parse(reader["DistrictId"].ToString());
                    dis.ProvinceID = int.Parse(reader["ProvinceID"].ToString());
                    dis.DistrictName = reader["DistrictName"].ToString();
                    dis.XCoor = reader["XCoor"].ToString();
                    dis.YCoor = reader["YCoor"].ToString();
                    dis.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    list.Add(dis);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<District> ListAllDistrict(int isVisible,ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<District> list = new List<District>();
                string sql = "select *from district where IsVisible=@isVisible";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@isVisible", isVisible);
                SqlCommand command = helper.GetCommand(sql,pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    District dis = new District();
                    dis.DistrictID = int.Parse(reader["DistrictId"].ToString());
                    dis.ProvinceID = int.Parse(reader["ProvinceID"].ToString());
                    dis.DistrictName = reader["DistrictName"].ToString();
                    dis.XCoor = reader["XCoor"].ToString();
                    dis.YCoor = reader["YCoor"].ToString();
                    dis.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    list.Add(dis);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<Province> ListAllProvince(int isVisible, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<Province> list = new List<Province>();
                string sql = "select *from province where IsVisible=@isVisible";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@isVisible", isVisible);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Province province = new Province();
                    province.ProvinceID = int.Parse(reader["ProvinceID"].ToString());
                    province.ProvinceName = reader["ProvinceName"].ToString();
                    province.Note = reader["Note"].ToString();
                    province.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    list.Add(province);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<District> ListAllDistrictFollowProvince(int provinceID,int isVisible, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<District> list = new List<District>();
                string sql = "select *from district where IsVisible=@isVisible and ProvinceID=@provinceID";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@isVisible", isVisible);
                pa[1] = new SqlParameter("@provinceID", provinceID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    District dis = new District();
                    dis.DistrictID = int.Parse(reader["DistrictId"].ToString());
                    dis.ProvinceID = int.Parse(reader["ProvinceID"].ToString());
                    dis.DistrictName = reader["DistrictName"].ToString();
                    dis.XCoor = reader["XCoor"].ToString();
                    dis.YCoor = reader["YCoor"].ToString();
                    dis.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    list.Add(dis);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<District> ListAllDistrictFollowProvince(int provinceID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<District> list = new List<District>();
                string sql = "select *from district where ProvinceID=@provinceID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@provinceID", provinceID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    District dis = new District();
                    dis.DistrictID = int.Parse(reader["DistrictId"].ToString());
                    dis.ProvinceID = int.Parse(reader["ProvinceID"].ToString());
                    dis.DistrictName = reader["DistrictName"].ToString();
                    dis.XCoor = reader["XCoor"].ToString();
                    dis.YCoor = reader["YCoor"].ToString();
                    dis.IsVisible = int.Parse(reader["IsVisible"].ToString());
                    list.Add(dis);
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static District GetDistrictDetail(int districtID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                District dis = null;
                string sql = "select *from district where DistrictID=@districtID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@districtID", districtID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    dis = new District();
                    dis.DistrictID = int.Parse(reader["DistrictId"].ToString());
                    dis.ProvinceID = int.Parse(reader["ProvinceID"].ToString());
                    dis.DistrictName = reader["DistrictName"].ToString();
                    dis.XCoor = reader["XCoor"].ToString();
                    dis.YCoor = reader["YCoor"].ToString();
                    dis.IsVisible = int.Parse(reader["IsVisible"].ToString());
                   
                }
                return dis;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool InserDistrictProduct(DistrictProduct disProduct, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "insert into districtproduct(ProductID,DistrictID) values(@productID,@districtID)";
                SqlParameter[] pa = new SqlParameter[2];
                pa[0] = new SqlParameter("@productID",disProduct.ProductID);
                pa[1] = new SqlParameter("@districtID",disProduct.DistrictID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static bool DeleteDistrictProductFollowProduct(int productID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                bool rs = false;
                string sql = "delete districtproduct where ProductID=@productID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@productID", productID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                int row = command.ExecuteNonQuery();
                if (row > 0)
                {
                    rs = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return false;
            }
            finally
            {
                helper.destroy();
            }
        }

        public static List<int> ListProductIDFollowDistrict(int districtID, ConnectionType typeConnect)
        {
            Sqlhelper helper = new Sqlhelper(typeConnect);
            try
            {
                List<int> list = new List<int>();
                string sql = "select ProductID from districtproduct where DistrictID=@districtID";
                SqlParameter[] pa = new SqlParameter[1];
                pa[0] = new SqlParameter("@districtID",districtID);
                SqlCommand command = helper.GetCommand(sql, pa, false);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(int.Parse(reader["ProductID"].ToString()));
                }
                return list;
            }
            catch (Exception ex)
            {
                Utilitys.WriteLog(ex.Message);
                return null;
            }
            finally
            {
                helper.destroy();
            }
        }
    }
}
